// 迁移脚本：确保PDF转换表结构正确
const database = require('../src/config/database');
const pool = database.default || database.pool;

async function migratePdfConversions() {
  const client = await pool.connect();
  
  try {
    console.log('开始PDF转换表迁移...');
    
    // 检查pdf_conversions表是否存在
    const tableExistsQuery = `
      SELECT EXISTS (
        SELECT FROM information_schema.tables 
        WHERE table_schema = 'public' 
        AND table_name = 'pdf_conversions'
      );
    `;
    
    const tableExists = await client.query(tableExistsQuery);
    
    if (!tableExists.rows[0].exists) {
      console.log('创建pdf_conversions表...');
      
      // 创建pdf_conversions表
      await client.query(`
        CREATE TABLE pdf_conversions (
          id SERIAL PRIMARY KEY,
          user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
          original_file_id INTEGER NOT NULL,
          pdf_path VARCHAR(500) NOT NULL,
          pdf_url VARCHAR(500) NOT NULL,
          conversion_status VARCHAR(20) DEFAULT 'processing',
          error_message TEXT,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          UNIQUE(original_file_id)
        );
      `);
      
      // 创建索引
      await client.query(`
        CREATE INDEX IF NOT EXISTS idx_pdf_conversions_original_file_id ON pdf_conversions(original_file_id);
      `);
      
      await client.query(`
        CREATE INDEX IF NOT EXISTS idx_pdf_conversions_user_id ON pdf_conversions(user_id);
      `);
      
      // 创建更新时间触发器
      await client.query(`
        CREATE TRIGGER update_pdf_conversions_updated_at 
        BEFORE UPDATE ON pdf_conversions 
        FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
      `);
      
      console.log('pdf_conversions表创建成功');
    } else {
      console.log('pdf_conversions表已存在，检查字段...');
      
      // 检查是否需要添加新字段
      const columnsQuery = `
        SELECT column_name 
        FROM information_schema.columns 
        WHERE table_name = 'pdf_conversions' 
        AND table_schema = 'public';
      `;
      
      const columns = await client.query(columnsQuery);
      const columnNames = columns.rows.map(row => row.column_name);
      
      // 检查必要字段是否存在
      const requiredColumns = [
        'id', 'user_id', 'original_file_id', 'pdf_path', 
        'pdf_url', 'conversion_status', 'error_message', 
        'created_at', 'updated_at'
      ];
      
      for (const column of requiredColumns) {
        if (!columnNames.includes(column)) {
          console.log(`添加缺失字段: ${column}`);
          
          switch (column) {
            case 'error_message':
              await client.query('ALTER TABLE pdf_conversions ADD COLUMN error_message TEXT;');
              break;
            case 'updated_at':
              await client.query('ALTER TABLE pdf_conversions ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;');
              break;
            // 添加其他可能缺失的字段
          }
        }
      }
    }
    
    // 检查attachments表是否存在，如果不存在则创建
    const attachmentsExistsQuery = `
      SELECT EXISTS (
        SELECT FROM information_schema.tables 
        WHERE table_schema = 'public' 
        AND table_name = 'attachments'
      );
    `;
    
    const attachmentsExists = await client.query(attachmentsExistsQuery);
    
    if (!attachmentsExists.rows[0].exists) {
      console.log('创建attachments表...');
      
      await client.query(`
        CREATE TABLE attachments (
          id SERIAL PRIMARY KEY,
          article_id INTEGER REFERENCES articles(id) ON DELETE CASCADE,
          user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
          file_name VARCHAR(255) NOT NULL,
          original_name VARCHAR(255) NOT NULL,
          file_type VARCHAR(50) NOT NULL,
          file_size BIGINT NOT NULL,
          file_url VARCHAR(500) NOT NULL,
          mime_type VARCHAR(100) NOT NULL,
          width INTEGER,
          height INTEGER,
          duration INTEGER,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
      `);
      
      // 创建索引
      await client.query(`
        CREATE INDEX IF NOT EXISTS idx_attachments_article_id ON attachments(article_id);
      `);
      
      await client.query(`
        CREATE INDEX IF NOT EXISTS idx_attachments_user_id ON attachments(user_id);
      `);
      
      console.log('attachments表创建成功');
    }
    
    console.log('PDF转换表迁移完成');
    
  } catch (error) {
    console.error('迁移失败:', error);
    throw error;
  } finally {
    client.release();
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  migratePdfConversions()
    .then(() => {
      console.log('迁移成功完成');
      process.exit(0);
    })
    .catch((error) => {
      console.error('迁移失败:', error);
      process.exit(1);
    });
}

module.exports = { migratePdfConversions };